﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;
using TuyenSinh.DLL;
using TuyenSinh.Ultilities;

namespace TuyenSinh.DAL
{
    public class DADetailEnrolling: BaseDAL
    {
        public DataSet GetMajor(int? iMajorId, string sName, int iSectionId, int iSchoolId, int isActive, int isDelete)
        {
            DataSet ds;
            DbCommand cmd;

            cmd = DB.GetStoredProcCommand("sp_GetMajor");
            if(iMajorId != 0)
                DB.AddInParameter(cmd, "@Id", SqlDbType.Int, iMajorId); 
            if (!string.IsNullOrEmpty(sName))
            {
                DB.AddInParameter(cmd, "@Name", SqlDbType.NVarChar, sName);                
            }
            if (iSchoolId != 0)
            {
                DB.AddInParameter(cmd, "@SchoolId", SqlDbType.Int, iSchoolId);                
            }
            if (iSectionId != 0)
            {
                DB.AddInParameter(cmd, "@SectionId", SqlDbType.Int, iSectionId);                
            }
           
            DB.AddInParameter(cmd, "@IsActive", SqlDbType.Bit, isActive == 1);
            DB.AddInParameter(cmd, "@IsDelete", SqlDbType.Bit, isDelete == 1);

            try
            {
                ds = DB.ExecuteDataSet(cmd);
                
            }
            catch (Exception ex)
            {
                Logger.FrameworkLogger.Error("System Error - get Major", ex);
                ds = null;
            }
            return ds;
        }

        public bool InsertDetailEnrolling(int iSchoolId, int iMajorId, int iField1, int iField2, int iNumRegister, int iTarget, double iScore, int iYear, float fNumCompetitor)
        {
            bool bResult = false;
            DbCommand cmd;

            cmd = DB.GetStoredProcCommand("sp_InsertDetailEnrolling");
            DB.AddInParameter(cmd, "@SchoolId", SqlDbType.Int, iSchoolId);
            DB.AddInParameter(cmd, "@MajorId", SqlDbType.Int, iMajorId);
            DB.AddInParameter(cmd, "@FieldId1", SqlDbType.Int, iField1);
            DB.AddInParameter(cmd, "@FieldId2", SqlDbType.Int, iField2);

            DB.AddInParameter(cmd, "@QuantityRegister", SqlDbType.Int, iNumRegister);
            DB.AddInParameter(cmd, "@Targets", SqlDbType.Int, iTarget);
            DB.AddInParameter(cmd, "@Score", SqlDbType.Float, iScore);
            DB.AddInParameter(cmd, "@Year", SqlDbType.Int, iYear);
            DB.AddInParameter(cmd, "@CompetitionNum", SqlDbType.Float, Convert.ToDouble(iNumRegister)/iTarget);

            
            try
            {
                DB.ExecuteNonQuery(cmd);
                bResult = true;
            }
            catch (Exception ex)
            {
                Logger.FrameworkLogger.Error("System Error - Insert Detail Enrolling", ex);
                bResult = false;
            }
            return bResult;
        }

        public DataSet GetSection(int iSectionId, string sName)
        {
            DataSet ds;
            DbCommand cmd;
            cmd = DB.GetStoredProcCommand("sp_GetSectionFilter");
            DB.AddInParameter(cmd, "@Id", SqlDbType.Int, iSectionId);
            if (!string.IsNullOrEmpty(sName))
            {
                DB.AddInParameter(cmd, "@Name", SqlDbType.NChar, sName);                
            }
            try
            {
                ds = DB.ExecuteDataSet(cmd);
            }
            catch (Exception ex)
            {
                Logger.FrameworkLogger.Error("System Error - GetSection", ex);
                ds = null;
            }
            return ds;
        }

        public DataSet GetDetailEnrolling(int iSchoolId, int iMajorId)
        {
            DataSet ds;
            DbCommand cmd;
            cmd = DB.GetStoredProcCommand("sp_GetDetailEnrolling");
            if (iMajorId!=0)
            {
                DB.AddInParameter(cmd, "@MajorId", SqlDbType.Int, iMajorId);                
            }
            if (iSchoolId != 0)
            {
                DB.AddInParameter(cmd, "@SchoolId", SqlDbType.Int, iSchoolId);
            }
            try
            {
                ds = DB.ExecuteDataSet(cmd);
            }
            catch (Exception ex)
            {
                Logger.FrameworkLogger.Error("System Error - Get Detail Enrolling", ex);
                ds = null;
            }
            return ds;
        }

        public bool UpdateDetailEnrolling(int iId, int iNumRegister, int iTargets, float iScore, float fCompetitor)
        {
            bool bRes;
            DbCommand cmd;
            cmd = DB.GetStoredProcCommand("sp_UpdateDetailEnrolling");
            if (iId != 0)
            {
                DB.AddInParameter(cmd, "@Id", SqlDbType.Int, iId);
            }
            if (iNumRegister != 0)
            {
                DB.AddInParameter(cmd, "@iNumRegister", SqlDbType.Int, iNumRegister);
            }
            if (iTargets != 0)
            {
                DB.AddInParameter(cmd, "@iTargets", SqlDbType.Int, iTargets);
            }
            if (iScore != 0)
            {
                DB.AddInParameter(cmd, "@iScore", SqlDbType.Float, iScore);
            }

            if (fCompetitor > 0)
            {
                DB.AddInParameter(cmd, "@fCompetitor", SqlDbType.Float, fCompetitor);
            }

            try
            {
                DB.ExecuteNonQuery(cmd);
                bRes = true;
            }
            catch (Exception ex)
            {
                bRes = false;
                Logger.FrameworkLogger.Error("System Error - Update Detail Enrolling", ex);                
            }
            return bRes;
        }
        //
        public DataSet GetInforMajorSchool(int iSchoolId, int iMajorId, int iFrom, int iTo)
        {
            DataSet ds;
            DbCommand cmd;
            cmd = DB.GetStoredProcCommand("sp_GetInforMajor1School");
            if (iSchoolId != 0)
            {
                DB.AddInParameter(cmd, "@SchoolId", SqlDbType.Int, iSchoolId);
            }
            if (iMajorId != 0)
            {
                DB.AddInParameter(cmd, "@MajorId", SqlDbType.Int, iMajorId);
            }
            if (iFrom != 0)
            {
                DB.AddInParameter(cmd, "@FromYear", SqlDbType.Int, iFrom);
            }
            if (iTo != 0)
            {
                DB.AddInParameter(cmd, "@ToYear", SqlDbType.Int, iTo);
            }
           
            try
            {
                ds = DB.ExecuteDataSet(cmd);
            }
            catch (Exception ex)
            {
                Logger.FrameworkLogger.Error("System Error - Get Detail Infor1 Enrolling", ex);
                ds = null;
            }
            return ds;
        }

        public DataSet GetMajorMultiSchool(int iMajorId, int iSchoolId, int iYear)
        {
            DataSet ds;
            DbCommand cmd;
            cmd = DB.GetStoredProcCommand("sp_GetDetailEnrolling");
            if (iMajorId != 0)
            {
                DB.AddInParameter(cmd, "@MajorId", SqlDbType.Int, iMajorId);
            }
            if (iSchoolId != 0)
            {
                DB.AddInParameter(cmd, "@SchoolId", SqlDbType.Int, iSchoolId);
            }
            if (iYear > 0)
            {
                DB.AddInParameter(cmd, "@iYear", SqlDbType.Int, iYear);                
            }
            try
            {
                ds = DB.ExecuteDataSet(cmd);
            }
            catch (Exception ex)
            {
                Logger.FrameworkLogger.Error("System Error - Get Detail Enrolling by Year", ex);
                ds = null;
            }
            return ds;
        }

        public DataSet GetPercentBetweenSchool(int iMajorGroupId, int iRegion, int iYear)
        {
            DataSet ds;
            DbCommand cmd;
            cmd = DB.GetStoredProcCommand("sp_GetInforMajorMultiSchool");
            if (iMajorGroupId != 0)
            {
                DB.AddInParameter(cmd, "@iMajorGroupId", SqlDbType.Int, iMajorGroupId);
            }
            if (iRegion != 0)
            {
                DB.AddInParameter(cmd, "@iRegion", SqlDbType.Int, iRegion);
            }
            if (iYear > 0)
            {
                DB.AddInParameter(cmd, "@iYear", SqlDbType.Int, iYear);
            }
            try
            {
                ds = DB.ExecuteDataSet(cmd);
            }
            catch (Exception ex)
            {
                Logger.FrameworkLogger.Error("System Error - Get Percent Major between School", ex);
                ds = null;
            }
            return ds;
        } 
        
        public DataSet GetPercentFieldBetweenSchool(int iRegion, int iYear)
        {
            DataSet ds;
            DbCommand cmd;
            cmd = DB.GetStoredProcCommand("sp_GetPercentFieldMultiSchool");
           
            if (iRegion != 0)
            {
                DB.AddInParameter(cmd, "@iRegion", SqlDbType.Int, iRegion);
            }
            if (iYear > 0)
            {
                DB.AddInParameter(cmd, "@iYear", SqlDbType.Int, iYear);
            }
            try
            {
                ds = DB.ExecuteDataSet(cmd);
            }
            catch (Exception ex)
            {
                Logger.FrameworkLogger.Error("System Error - Get Percent Field between School", ex);
                ds = null;
            }
            return ds;
        }
    }
}
